USE [SSB]
GO

--INSERT new publication request
CREATE PROCEDURE [SSBPubSub].[usp_AddPublication]
	@publication uniqueidentifier,
	@subject varchar(50),
	@Message_body xml
	,@Server varchar(30)
    ,@FromService varchar(100)
	,@ToService varchar(100)
	,@Action char(8)
AS
BEGIN
   SET NOCOUNT ON;
   DECLARE @Error_Description varchar(2000); 
   BEGIN TRY
    IF NOT EXISTS(SELECT * FROM [SSBPubSub].[publications_Record] 
					 WHERE [Server]=@Server 
                          AND [FromService]=@FromService
                          AND [ToService]=@ToService
						  AND [subject]=@subject
						  AND [Action]=@Action)
    BEGIN
	INSERT INTO [SSBPubSub].[Publications_Record]
		([Conversation_handle], [subject], [Message_Body],[Server],[FromService],[ToService] ,[Action],[Indate])
		VALUES (@publication, UPPER(@subject), @Message_body,@Server,@FromService,@ToService,UPPER(@Action),Getdate());
    END
   	END TRY
	BEGIN CATCH
		BEGIN
				SET   @Error_Description=
								 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10))+
								 'Error Message : ' + ERROR_MESSAGE()+
								 'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10))+
								 'Error State: ' + CAST(ERROR_STATE() AS VARCHAR(10))+
								 'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR(10))+
								 'Error Proc: ' + ISNULL(ERROR_PROCEDURE(), 'Not within proc');
				INSERT INTO [SSBPubSub].Exception_Record (message_body,InDate,ErrorInfo) 
				VALUES (@Message_body,getdate(),@Error_Description);
			END
	END CATCH
END

GO


--INSERT  new subscriber record
CREATE PROCEDURE [SSBPubSub].[usp_AddSubscriber]
	@Conversation_Handle uniqueidentifier,
	@subject varchar(50),
	@Message_body xml
	,@Server varchar(30)
	,@FromService varchar(100)
	,@toService varchar(100)
	,@Action char(8)
AS

BEGIN
	SET NOCOUNT ON;
	DECLARE @Error_Description VARCHAR(2000);
	BEGIN TRY
		IF NOT EXISTS(SELECT * FROM [SSBPubSub].[Subscriptions_Record] 
						 WHERE [Server]=@Server 
							  AND [ToService]=@ToService
							AND [FromService]=@FromService
							  AND [subject]=@subject
							  AND [Action]=@Action)
		BEGIN
		INSERT INTO [SSBPubSub].[Subscriptions_Record]
			([Conversation_handle], [subject], [Message_body],[Server],[FromService],[ToService] ,[Action],[Indate] )
			VALUES (@Conversation_handle, UPPER(@subject), @Message_body,@Server,@FromService,@ToService,UPPER(@Action),getdate());
		END
	END TRY
	BEGIN CATCH
		BEGIN
				SET   @Error_Description=
								 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10))+
								 'Error Message : ' + ERROR_MESSAGE()+
								 'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10))+
								 'Error State: ' + CAST(ERROR_STATE() AS VARCHAR(10))+
								 'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR(10))+
								 'Error Proc: ' + ISNULL(ERROR_PROCEDURE(), 'Not within proc');
				INSERT INTO [SSBPubSub].Exception_Record (message_body,InDate,ErrorInfo) 
				VALUES (@Message_body,getdate(),@Error_Description);
			END
	END CATCH
END

GO


CREATE PROCEDURE [SSBPubSub].[usp_ProcessPublicationRequest]
	@CONVERSATION uniqueidentifier,
	@RequestMSG xml
AS
BEGIN
 	SET NOCOUNT ON;
	DECLARE @subject varchar(50)
            ,@Server varchar(30)
            ,@FromService varchar(100)
			,@ToService varchar(100)
            ,@Action char(8)
			,@Error_Description varchar(2000);
	
	BEGIN TRY	
		;WITH XMLNAMESPACES (DEFAULT 'http://ssbrouter/v10/SSBPubSubService') 
			SELECT @subject = @RequestMSG.value(N'(/Publish/Subject)[1]',N'varchar(50)')
				,@Server=@RequestMSG.value(N'(/Publish/Server)[1]', N'varchar(30)')
				,@FromService=@RequestMSG.value(N'(/Publish/FromService)[1]', N'varchar(100)')
				,@ToService=@RequestMSG.value(N'(/Publish/ToService)[1]', N'varchar(100)')
				,@Action=@RequestMSG.value(N'(/Publish/Action)[1]', N'char(8)');
		IF (@subject IS NOT NULL)
		BEGIN
		  IF(@Action='ADD')
		   BEGIN
					EXEC [SSBPubSub].[usp_AddPublication]
				@CONVERSATION, @subject, @RequestMSG,@Server,@FromService,@ToService,@Action;		
				END	
				Else IF(@Action='REMOVE')
				 BEGIN
				EXEC [SSBPubSub].usp_RemovePublication @subject, @Server,@FromService,@ToService;	
				END	
		END
		ELSE
		BEGIN
					EXEC [SSBPubSub].usp_RemovePublication @subject, @Server,@FromService,@ToService;
		END
		END TRY
		BEGIN CATCH
			BEGIN
					SET   @Error_Description=
									 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10))+
									 'Error Message : ' + ERROR_MESSAGE()+
									 'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10))+
									 'Error State: ' + CAST(ERROR_STATE() AS VARCHAR(10))+
									 'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR(10))+
									 'Error Proc: ' + ISNULL(ERROR_PROCEDURE(), 'Not within proc');
					INSERT INTO [SSBPubSub].Exception_Record (message_body,InDate,Conversation_handle,ErrorInfo) 
					VALUES (@RequestMSG,getdate(),@CONVERSATION,@Error_Description);
				END
		END CATCH
END

GO


CREATE PROCEDURE [SSBPubSub].[usp_ProcessSubscriptionRequest]
	@CONVERSATION uniqueidentifier,
	@request xml
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @subject varchar(50)
            ,@Server varchar(30)
            ,@FromService varchar(100)
			,@ToService varchar(100)
            ,@Action char(8)
			,@Error_Description VARCHAR(2000);
	
	WITH XMLNAMESPACES (DEFAULT 'http://ssbrouter/v10/SSBPubSubService') 
		SELECT @subject = @request.value(N'(/Request/Subject)[1]',N'varchar(50)')
            ,@Server=@request.value(N'(/Request/Server)[1]', N'varchar(30)')
            ,@FromService=@request.value(N'(/Request/FromService)[1]', N'varchar(100)')
            ,@ToService=@request.value(N'(/Request/ToService)[1]', N'varchar(100)')
            ,@Action=@request.value(N'(/Request/Action)[1]', N'char(8)');
	BEGIN TRY
		IF (@subject IS NOT NULL)
		BEGIN
		 	 IF(@Action='ADD')
					BEGIN
								EXEC [SSBPubSub].[usp_AddSubscriber] @CONVERSATION, @subject, @request,@Server,@FromService,@ToService,@Action;
					END
			 ELSE IF(@Action='REMOVE')
			 		BEGIN
								EXEC [SSBPubSub].[usp_RemoveSubscriber] @CONVERSATION, @subject, @request,@Server,@FromService,@ToService;
					END			 
		END
		ELSE
		BEGIN
			END CONVERSATION @CONVERSATION WITH error = 2 description = N'The subscription request is missing a subject';
			EXEC [SSBPubSub].[usp_RemoveSubscriber] @subject, @Server,@FromService,@ToService;
		END
	END TRY
	BEGIN CATCH
		BEGIN
				SET   @Error_Description=
								 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10))+
								 'Error Message : ' + ERROR_MESSAGE()+
								 'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10))+
								 'Error State: ' + CAST(ERROR_STATE() AS VARCHAR(10))+
								 'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR(10))+
								 'Error Proc: ' + ISNULL(ERROR_PROCEDURE(), 'Not within proc');
				INSERT INTO [SSBPubSub].Exception_Record (message_body,InDate,Conversation_handle,ErrorInfo) 
				VALUES (@request,getdate(),@CONVERSATION,@Error_Description);
			END
	END CATCH
END

GO


--Remove publication request
CREATE PROCEDURE [SSBPubSub].[usp_RemovePublication]
	@subject VARCHAR(50)
	,@Server VARCHAR(30)
    ,@FromService VARCHAR(100)
	,@ToService VARCHAR(100)
AS

BEGIN
	SET NOCOUNT ON;
  DELETE FROM [SSBPubSub].[Publications_Record]
  WHERE       [Server] = @Server
              AND [FromService] = @FromService
              AND [ToService] = @ToService
              AND [Subject] = @subject;
END

GO


--Remove subscriber record
CREATE PROCEDURE [SSBPubSub].[usp_RemoveSubscriber]
	@subject varchar(50)
	,@Server varchar(30)
    ,@FromService varchar(100)
	,@ToService varchar(100)
AS
BEGIN
	SET NOCOUNT ON;
  DELETE FROM [SSBPubSub].[Subscriptions_Record]
  WHERE       [Server] = @Server
              AND [FromService] = @FromService
              AND [ToService] = @ToService
              AND [Subject] = @subject;
END

GO


CREATE PROCEDURE [SSBPubSub].[usp_RoutingMsg]
AS

BEGIN
	SET NOCOUNT ON;
	DECLARE @CONVERSATION uniqueidentifier;
	DECLARE @MESSAGE xml;
	DECLARE @message_type_name sysname;
    DECLARE @Error_Description varchar(2000);
	WAITFOR (receive TOP(1) @CONVERSATION = conversation_handle,
		@MESSAGE = message_body,
		@message_type_name = message_type_name
		FROM [http://ssbrouter/v10/SSBPubSubQueue]), timeout 1000;
	WHILE (@CONVERSATION IS NOT NULL)
	BEGIN
        BEGIN TRY
			IF (@message_type_name=N'http://ssbrouter/v10/Types/SSBArticleMsg' )
			BEGIN
				EXEC [SSBPubSub].[usp_SendToSubscriptions]  @MESSAGE;
				END CONVERSATION @CONVERSATION;
			END
			ELSE IF (@message_type_name = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
			BEGIN
				END CONVERSATION @CONVERSATION;
			END
			ELSE IF (@message_type_name=N'http://schemas.microsoft.com/SQL/ServiceBroker/Error')
			BEGIN
				INSERT INTO [SSBPubSub].Exception_Record (message_body,InDate,Conversation_handle,ErrorInfo) 
					VALUES (@MESSAGE,getdate(),@CONVERSATION,'Error Occur');
				END CONVERSATION @CONVERSATION;
				
			END
			ELSE IF (@message_type_name=N'http://ssbrouter/v10/Types/SSBPublishMsg')
			BEGIN
				EXEC [SSBPubSub].[usp_ProcessPublicationRequest] @CONVERSATION, @MESSAGE;
				END CONVERSATION @CONVERSATION;
			END
			ELSE IF (@message_type_name=N'http://ssbrouter/v10/Types/SSBSubscribeMsg')
			BEGIN
				EXEC [SSBPubSub].[usp_ProcessSubscriptionRequest] @CONVERSATION, @MESSAGE;
				END CONVERSATION @CONVERSATION;
			END
			ELSE
			BEGIN 
				-- unexpected MESSAGE
				END CONVERSATION @CONVERSATION;
				RAISERROR (N'Received unexpected MESSAGE TYPE: %s', 16, 1, @message_type_name);
			END
     	END TRY
		BEGIN CATCH
			BEGIN
					SET   @Error_Description=
									 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10))+
									 'Error Message : ' + ERROR_MESSAGE()+
									 'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10))+
									 'Error State: ' + CAST(ERROR_STATE() AS VARCHAR(10))+
									 'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR(10))+
									 'Error Proc: ' + ISNULL(ERROR_PROCEDURE(), 'Not within proc');
					INSERT INTO [SSBPubSub].Exception_Record (message_body,InDate,Conversation_handle,ErrorInfo) 
					VALUES (@MESSAGE,getdate(),@CONVERSATION,@Error_Description);
				END
		END CATCH
		
		SELECT @CONVERSATION = NULL;

		WAITFOR (receive TOP(1) @CONVERSATION = conversation_handle,
			@MESSAGE = message_body,
			@message_type_name = message_type_name
			FROM [http://ssbrouter/v10/SSBPubSubQueue]), timeout 1000;
	END

END

GO


CREATE PROCEDURE [SSBPubSub].[usp_SendToSubscriptions]
	@article xml
AS

BEGIN
	SET NOCOUNT ON;
	DECLARE @subscription uniqueidentifier
			,@FromService varchar(100)
			,@ToService varchar(100)
			,@Subject varchar(50)		
			,@Counter int
			,@error int;
	SET @Counter=1;
;	WITH XMLNAMESPACES (DEFAULT 'http://ssbrouter/v10/SSBPubSubService') 
		SELECT @Subject = @article.value(N'(/Publish/Subject)[1]',N'varchar(50)');

IF(@Subject='' or @Subject is null)
		BEGIN
				INSERT INTO [SSBPubSub].Exception_Record (message_body,InDate,Conversation_handle,ErrorInfo) 
				VALUES (@article,getdate(),NEWID(),'No Subject or Subject is null'); 
		END

	    DECLARE @Error_Description varchar(2000)
		DECLARE @crsSubscriptions CURSOR;
		SET @crsSubscriptions = CURSOR  local static  Forward_only read_only  FOR
			SELECT distinct s.FromService,s.ToService 
				FROM [SSBPubSub].[Subscriptions_Record] s 
				JOIN [SSBPubSub].[Publications_Record] p ON s.subject = p.subject
				and p.subject=@Subject;

		OPEN @crsSubscriptions;

        IF(@@CURSOR_ROWS=0)
		BEGIN
				INSERT INTO [SSBPubSub].Exception_Record (message_body,InDate,Conversation_handle,ErrorInfo) 
				VALUES (@article,getdate(),NEWID(),'can not find subscriber'); 
		END

		FETCH next FROM @crsSubscriptions
			INTO @FromService,@ToService;
		WHILE (0 = @@fetch_status)
		BEGIN
             BEGIN TRY
 					BEGIN DIALOG CONVERSATION @subscription
					FROM SERVICE @FromService
					TO SERVICE @ToService
					ON CONTRACT [http://ssbrouter/v10/SSBSubscribeContract];
 
				;SEND ON CONVERSATION @subscription 
				 MESSAGE TYPE [http://ssbrouter/v10/Types/SSBArticleMsg] (@article);
	            SELECT @error=@@ERROR;
                IF @error=0
                  BEGIN
					INSERT [SSBPubSub].[Send_Record]([Conversation_handle],Message_body,Indate)
					VALUES(@subscription,@article,Getdate())					  					
				  END   
				END TRY
				BEGIN CATCH
					BEGIN
							SET   @Error_Description=
											 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10))+
											 'Error Message : ' + ERROR_MESSAGE()+
											 'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10))+
											 'Error State: ' + CAST(ERROR_STATE() AS VARCHAR(10))+
											 'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR(10))+
											 'Error Proc: ' + ISNULL(ERROR_PROCEDURE(), 'Not within proc');
							INSERT INTO [SSBPubSub].Exception_Record (message_body,InDate,Conversation_handle,ErrorInfo,FromService,ToService) 
							VALUES (@article,getdate(),@subscription,@Error_Description,@FromService,@ToService);
						 
						END
				END CATCH;
  
			 SELECT @subscription = NULL;
			FETCH next FROM @crsSubscriptions
				INTO @FromService,@ToService;
		END

		CLOSE @crsSubscriptions;
		DEALLOCATE @crsSubscriptions;

END

GO